package com.gl.basis.common.poi.annotation;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.util.*;

import static org.apache.poi.ss.usermodel.CellType.*;

public class ExcelUtils {

    // excel2003扩展名
    public static final String EXCEL03_EXTENSION = ".xls";
    // excel2007扩展名
    public static final String EXCEL07_EXTENSION = ".xlsx";

    // 获取Excel处理类
    private static ExcelBase getExcelHelper(String fileName) {
        ExcelBase helper = null;
        if (fileName.endsWith(EXCEL03_EXTENSION)) {
            helper = new Excel2k3Base();
        } else if (fileName.endsWith(EXCEL07_EXTENSION)) {
            helper = new Excel2k7Base();
        }
        return helper;
    }


    /**
     * @param pojoClass
     * @param rowspan   跳过多少行
     * @return
     */
    public static <T> List<T> importExcelInConmmissionUser(String fileName, InputStream fileIs, Class<T> pojoClass, int rowspan) throws Exception {
        if (rowspan < 0) rowspan = 0;
        List dist = new ArrayList<Object>();
        // 得到目标目标类的所有的字段列表
        Field filed[] = pojoClass.getDeclaredFields();
        // 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中
        Map<String, Method> fieldSetMap = new HashMap<String, Method>();
        Map<String, Method> fieldSetConvertMap = new HashMap<String, Method>();
        // 循环读取所有字段
        for (int i = 0; i < filed.length; i++) {
            Field f = filed[i];
            // 得到单个字段上的Annotation
            ExcelAnnotation excel = f.getAnnotation(ExcelAnnotation.class);
            // 如果标识了Annotationd的话
            if (excel != null) {
                // 构造设置了Annotation的字段的Setter方法
                String fieldname = f.getName();
                String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
                // 构造调用的method，
                Method setMethod = pojoClass.getMethod(setMethodName, new Class[]{f.getType()});
                // 将这个method以Annotaion的名字为key来存入。
                // 对于重名将导致 覆盖 失败，对于此处的限制需要
                fieldSetMap.put(excel.name(), setMethod);
                if (excel.importConvertSign() == 1) {
                    StringBuffer setConvertMethodName = new StringBuffer("set");
                    setConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());
                    setConvertMethodName.append(fieldname.substring(1));
                    setConvertMethodName.append("Convert");
                    Method getConvertMethod = pojoClass.getMethod(setConvertMethodName.toString(),
                            new Class[]{String.class});
                    fieldSetConvertMap.put(excel.name(), getConvertMethod);
                }
            }
        }

        ExcelBase excelHelper = getExcelHelper(fileName);
        // // 得到工作表
        Workbook book = excelHelper.readWorkbookFromExcel(fileIs);
        // // 得到第一页
        Sheet sheet = book.getSheetAt(0);
        // // 得到第一面的所有行
        Iterator<Row> row = sheet.rowIterator();

        // 跳过多少行
        for (int i = 0; i < rowspan; i++) {
            row.next();
        }

        // 得到表头行
        Row head = row.next();
        Iterator<Cell> cellTitle = head.cellIterator();
        // 将表头的列放入到一个map中。
        Map headMap = new HashMap();
        int i = 0;
        while (cellTitle.hasNext()) {
            Cell cell = cellTitle.next();
            String value = String.valueOf(cell.getStringCellValue());
            headMap.put(i, value);
            i = i + 1;
        }

        //解析数据行
        while (row.hasNext()) {
            Row rown = row.next();
            if (ExcelUtils.isBlankRow(rown)) {
                continue;
            }
            // 行的所有列
            Iterator<Cell> cellbody = rown.cellIterator();
            // 得到传入类的实例
            Object tObject = pojoClass.newInstance();

            for (int j = rown.getFirstCellNum(); j <= rown.getLastCellNum(); j++) {
                Cell cell = rown.getCell(j);
                if (cell == null) {
                    continue;
                }
                String titleString = (String) headMap.get(j);
                // 如果这一列的标题和类中的某一列的Annotation相同，那么则调用此类的的set方法，进行设值
                if (!fieldSetMap.containsKey(titleString)) continue;

                Method setMethod = (Method) fieldSetMap.get(titleString);
                // 得到setter方法的参数
                Type[] ts = setMethod.getGenericParameterTypes();
                // 只要一个参数
                String xclass = ts[0].toString();

                try {
                    // 如果是转换的方法就直接执行转换的方法
                    if (fieldSetConvertMap.containsKey(titleString)) {
                        if (cell.getStringCellValue() == null) continue;
                        fieldSetConvertMap.get(titleString).invoke(tObject, cell.getStringCellValue().trim());
                        continue;
                    }

                    //设置值
                    if (xclass.equals("class java.lang.String")) {
                        // 先设置Cell的类型，然后就可以把纯数字作为String类型读进来了：
                        cell.setCellType(STRING);
                        if (cell.getStringCellValue() != null && cell.getStringCellValue().trim().length() != 0) {
                            String temp = cell.getStringCellValue().trim();
                            setMethod.invoke(tObject, temp);
                        }
                        continue;
                    }

                    if (xclass.equals("class java.util.Date")) {
                        setMethod.invoke(tObject, cell.getDateCellValue());
                    }

                    if (xclass.equals("class java.lang.Boolean")) {
                        if (cell.getCellType() == BOOLEAN) {
                            setMethod.invoke(tObject, cell.getBooleanCellValue());
                        }
                        continue;
                    }

                    if (xclass.equals("class java.lang.Integer")) {
                        if (cell.getCellType() == NUMERIC) {
                            setMethod.invoke(tObject, new Integer((int) cell.getNumericCellValue()));
                        } else if (cell.getCellType() == STRING) {
                            if (cell.getStringCellValue() == null) continue;
                            setMethod.invoke(tObject, new Integer(cell.getStringCellValue().trim()));
                        }
                        continue;
                    }

                    if (xclass.equals("class java.lang.Long")) {
                        if (cell.getCellType() == NUMERIC) {
                            setMethod.invoke(tObject, new Long((long) cell.getNumericCellValue()));
                        } else if (cell.getCellType() == STRING) {
                            if (cell.getStringCellValue() == null) continue;
                            setMethod.invoke(tObject, new Long(cell.getStringCellValue().trim()));
                        }
                        continue;
                    }

                    if (("class java.math.BigDecimal").equals(xclass)) {
                        Double number = 0.0;
                        // 判断cell类型是否为number型
                        if (cell.getCellType() != NUMERIC) {
                            if (cell.getStringCellValue() == null) continue;
                            number = Double.valueOf(cell.getStringCellValue().trim());
                        } else {
                            number = Double.valueOf(cell.getNumericCellValue());
                        }
                        setMethod.invoke(tObject, new BigDecimal(number.toString()));
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

            if (tObject != null) {
                dist.add(tObject);
            }
        }
        return dist;
    }

    /**
     * @param title     Sheet名字
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     * @param out       输出流
     */
    public static void exportExcel(String title, Class<?> pojoClass, Collection<?> dataSet, OutputStream out) {
        // 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出
        exportExcelInUserModel(title, pojoClass, dataSet, out);
        // 使用eventModel实现，可以一边读一边处理，效率较高，但是实现复杂，暂时未实现
    }

    private static void exportExcelInUserModel(String title, Class<?> pojoClass, Collection<?> dataSet, OutputStream out) {
        try {
            // 首先检查数据看是否是正确的
            if (dataSet == null || dataSet.size() == 0) {
                throw new Exception("导出数据为空！");
            }
            if (title == null || out == null || pojoClass == null) {
                throw new Exception("传入参数不能为空！");
            }
            // 声明一个工作薄
            Workbook workbook = new HSSFWorkbook();
            // 生成一个表格
            Sheet sheet = workbook.createSheet(title);

            // 标题
            List<String> exportFieldTitle = new ArrayList<String>();
            List<Integer> exportFieldWidth = new ArrayList<Integer>();
            // 拿到所有列名，以及导出的字段的get方法
            List<Method> methodObj = new ArrayList<Method>();
            Map<String, Method> convertMethod = new HashMap<String, Method>();
            // 得到所有字段
            Field fileds[] = pojoClass.getDeclaredFields();
            // 遍历整个filed
            for (int i = 0; i < fileds.length; i++) {
                Field field = fileds[i];
                ExcelAnnotation excel = field.getAnnotation(ExcelAnnotation.class);
                // 如果设置了annottion
                if (excel != null) {
                    // 添加到标题
                    exportFieldTitle.add(excel.name());
                    // 添加标题的列宽
                    exportFieldWidth.add(excel.exportFieldWidth());
                    // 添加到需要导出的字段的方法
                    String fieldname = field.getName();
                    // System.out.println(i+"列宽"+excel.exportName()+" "+excel.exportFieldWidth());
                    StringBuffer getMethodName = new StringBuffer("get");
                    getMethodName.append(fieldname.substring(0, 1).toUpperCase());
                    getMethodName.append(fieldname.substring(1));

                    Method getMethod = pojoClass.getMethod(getMethodName.toString(), new Class[]{});

                    methodObj.add(getMethod);
                    if (excel.exportConvertSign() == 1) {
                        StringBuffer getConvertMethodName = new StringBuffer("get");
                        getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());
                        getConvertMethodName.append(fieldname.substring(1));
                        getConvertMethodName.append("Convert");
                        // System.out.println("convert: "+getConvertMethodName.toString());
                        Method getConvertMethod = pojoClass.getMethod(getConvertMethodName.toString(), new Class[]{});
                        convertMethod.put(getMethodName.toString(), getConvertMethod);
                    }
                }
            }
            int index = 0;
            // 产生表格标题行
            Row row = sheet.createRow(index);
            for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {
                Cell cell = row.createCell(i);
                // cell.setCellStyle(style);
                RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i));
                cell.setCellValue(text);
            }

            // 设置每行的列宽
            for (int i = 0; i < exportFieldWidth.size(); i++) {
                // 256=65280/255
                sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));
            }
            Iterator its = dataSet.iterator();
            // 循环插入剩下的集合
            while (its.hasNext()) {
                // 从第二行开始写，第一行是标题
                index++;
                row = sheet.createRow(index);
                Object t = its.next();
                for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) {
                    Cell cell = row.createCell(k);
                    Method getMethod = methodObj.get(k);
                    Object value = null;
                    if (convertMethod.containsKey(getMethod.getName())) {
                        Method cm = convertMethod.get(getMethod.getName());
                        value = cm.invoke(t, new Object[]{});
                    } else {
                        value = getMethod.invoke(t, new Object[]{});
                    }
                    cell.setCellValue(value == null ? "" : value.toString());
                }
            }

            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static boolean isBlankRow(Row row) {
        if (row == null) return true;
        boolean result = true;
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.createCell(i);
            String value = "";
            if (cell != null) {
                switch (cell.getCellType()) {
                    case STRING:
                        value = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        value = String.valueOf((int) cell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        value = String.valueOf(cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        value = String.valueOf(cell.getCellFormula());
                        break;
                    //case Cell.CELL_TYPE_BLANK:
                    //  break;
                    default:
                        break;
                }

                if (!value.trim().equals("")) {
                    result = false;
                    break;
                }
            }
        }

        return result;
    }
}